<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>数据库关系</title>
    <link rel="stylesheet" href="/common/bootstrap.min.css">
    <link rel="stylesheet" href="/common/bootstrap-select.min.css">
</head>
<body>
<div class="container" style="">
    <h1>建立关系</h1>
    <div class="row">
        <div class="col-sm-4">
            <div class="form-group">
                <label>数据库</label>
                <select id="db_ship1" class="selectpicker" >
                    <option value="">请选择数据库</option>
                </select>
            </div>
        </div>
        <div class="col-sm-4">
            <div class="form-group">
                <label>数据表</label>
                <select id="table_ship1" class="selectpicker" data-live-search="true" disabled>
                    <option value="">请先选择数据库</option>
                </select>
            </div>
        </div>
        <div class="col-sm-4">
            <div class="form-group">
                <label>字段</label>
                <select id="field_ship1" class="selectpicker" data-live-search="true" disabled>
                    <option value="">请先选择数据表</option>
                </select>
            </div>
        </div>
        <input id="hiddenId_ship1" type="text" class="form-control" style="display: none">
    </div>

    <div class="row">
        <div class="col-sm-4">
            <div class="form-group">
                <label>数据库</label>
                <select id="db_ship2" class="selectpicker" >
                    <option value="">请选择数据库</option>
                </select>
            </div>
        </div>
        <div class="col-sm-4">
            <div class="form-group">
                <label>数据表</label>
                <select id="table_ship2" class="selectpicker" data-live-search="true" disabled>
                    <option value="">请先选择数据库</option>
                </select>
            </div>
        </div>
        <div class="col-sm-4">
            <div class="form-group">
                <label>字段</label>
                <select id="field_ship2" class="selectpicker" data-live-search="true" disabled>
                    <option value="">请先选择数据表</option>
                </select>
            </div>
        </div>
        <input id="hiddenId_ship2" type="text" class="form-control" style="display: none">
    </div>

    <div class="row">
        <div class="col-sm-12">
            <button class="btn btn-primary" onclick="creatShip()">建立联系</button>
        </div>
    </div>


    <br>
    <br>
    <br>
    <br>
    <br>

    <h1>查询关系</h1>
    <div class="row">
        <div class="col-sm-4">
            <div class="form-group">
                <label>数据库</label>
                <select id="db" class="selectpicker" >
                    <option value="">请选择数据库</option>
                </select>
            </div>
        </div>
        <div class="col-sm-4">
            <div class="form-group">
                <label>数据表</label>
                <select id="table" class="selectpicker" data-live-search="true" disabled>
                    <option value="">请先选择数据库</option>
                </select>
            </div>
        </div>
        <div class="col-sm-4">
            <div class="form-group">
                <label>字段</label>
                <select id="field" class="selectpicker" data-live-search="true" disabled>
                    <option value="">请先选择数据表</option>
                </select>
            </div>
        </div>
        <input id="hiddenId" type="text" class="form-control" style="display: none">
    </div>

    <div class="row">
        <div class="col-sm-12">
            <button class="btn btn-primary" onclick="query()">查询</button>
            <button style="margin-left: 150px" class="btn btn-default" onclick="reset()">清空</button>
        </div>
    </div>

    <br>
    <br>
    <br>
    <br>
    <table class="table table-hover" id="result_table">
        <thead>
        <tr>
            <th>关系ID</th>
            <th>DB</th>
            <th>Table</th>
            <th>Field</th>
            <th>Note</th>
            <th>操作</th>
        </tr>
        </thead>
        <tbody id="result">
        </tbody>
    </table>



    <br>
    <br>
    <br>
    <br>
    <br>
    <br>
    <h1>查询关系树</h1>
    <div class="row">
        <div class="col-sm-4">
            <div class="form-group">
                <label>数据库</label>
                <select id="db_ship_tree1" class="selectpicker" >
                    <option value="">请选择数据库</option>
                </select>
            </div>
        </div>
        <div class="col-sm-4">
            <div class="form-group">
                <label>数据表</label>
                <select id="table_ship_tree1" class="selectpicker" data-live-search="true" disabled>
                    <option value="">请先选择数据库</option>
                </select>
            </div>
        </div>
        <div class="col-sm-4">
            <div class="form-group">
                <label>字段</label>
                <select id="field_ship_tree1" class="selectpicker" data-live-search="true" disabled>
                    <option value="">请先选择数据表</option>
                </select>
            </div>
        </div>
    </div>

    <div class="row">
        <div class="col-sm-4">
            <div class="form-group">
                <label>数据库</label>
                <select id="db_ship_tree2" class="selectpicker" >
                    <option value="">请选择数据库</option>
                </select>
            </div>
        </div>
        <div class="col-sm-4">
            <div class="form-group">
                <label>数据表</label>
                <select id="table_ship_tree2" class="selectpicker" data-live-search="true" disabled>
                    <option value="">请先选择数据库</option>
                </select>
            </div>
        </div>
        <div class="col-sm-4">
            <div class="form-group">
                <label>字段</label>
                <select id="field_ship_tree2" class="selectpicker" data-live-search="true" disabled>
                    <option value="">请先选择数据表</option>
                </select>
            </div>
        </div>
    </div>

    <div class="row">
        <div class="col-sm-12">
            <button class="btn btn-primary" onclick="searchTree()">查找关联树</button>
        </div>
    </div>
    <div id="outputTree"></div>


</div>

<script src="/common/jquery.min.js"></script>
<script src="/common/bootstrap.min.js"></script>
<script src="/common/bootstrap-select.min.js"></script>
<script src="config.js"></script>
<script>
    $("#result_table").hide();
    //全局保存数据库表字段避免重复查找
    var dbJson;
    //关系json全局保存
    var queryResult;

    $(function() {
        //初始化db的那个框，只要有的都要来这里配置
        $.getJSON(serverURL+'search', function(data) {
            var dbs = [];
            dbJson = data;
            $.each(data, function(key, value) {
                if ($.inArray(value.db, dbs) === -1) {
                    dbs.push(value.db);
                }
            });
            $.each(dbs, function(key, value) {
                $('#db').append('<option value="' + value + '">' + value + '</option>');
                $('#db_ship1').append('<option value="' + value + '">' + value + '</option>');
                $('#db_ship2').append('<option value="' + value + '">' + value + '</option>');
                $('#db_ship_tree1').append('<option value="' + value + '">' + value + '</option>');
                $('#db_ship_tree2').append('<option value="' + value + '">' + value + '</option>');
            });
            $('#db').prop('disabled', false).selectpicker('refresh');
            $('#db_ship1').prop('disabled', false).selectpicker('refresh');
            $('#db_ship2').prop('disabled', false).selectpicker('refresh');
            $('#db_ship_tree1').prop('disabled', false).selectpicker('refresh');
            $('#db_ship_tree2').prop('disabled', false).selectpicker('refresh');
        });
        $('#db').change(function() {
            var selectedDb = $(this).val();
            var tables = [];
            $.each(dbJson, function(key, value) {
                if (value.db === selectedDb) {
                    if ($.inArray(value.table, tables) === -1) {
                        tables.push(value.table);
                    }
                }
            });
            $('#table').empty().append('<option value="">请选择数据表</option>');
            $.each(tables, function(key, value) {
                $('#table').append('<option value="' + value + '">' + value + '</option>');
            });
            $('#table').prop('disabled', false).selectpicker('refresh');
            $('#field').empty().append('<option value="">请先选择数据表</option>').prop('disabled', true).selectpicker('refresh');
        });

        $('#table').change(function() {
            var selectedDb = $('#db').val();
            var selectedTable = $(this).val();
            var fields = [];
            $.each(dbJson, function(key, value) {
                if (value.db === selectedDb && value.table === selectedTable) {
                    fields.push(value.field);
                }
            });
            $('#field').empty().append('<option value="">请选择字段</option>');
            $.each(fields, function(key, value) {
                $('#field').append('<option value="' + value + '">' + value + '</option>');
            });
            $('#field').prop('disabled', false).selectpicker('refresh');
        });

        $('#field').change(function() {
            var selectedDb = $('#db').val();
            var selectedTable = $('#table').val();
            var selectedField = $(this).val();
            $.each(dbJson, function(key, value) {
                if (value.db === selectedDb && value.table === selectedTable && value.field === selectedField) {
                    $('#hiddenId').val(value.id);
                }
            });
        });
        /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        $('#db_ship1').change(function() {
            var selecteddb_ship1 = $(this).val();
            var table_ship1 = [];
            $.each(dbJson, function(key, value) {
                if (value.db === selecteddb_ship1) {
                    if ($.inArray(value.table, table_ship1) === -1) {
                        table_ship1.push(value.table);
                    }
                }
            });
            $('#table_ship1').empty().append('<option value="">请选择数据表</option>');
            $.each(table_ship1, function(key, value) {
                $('#table_ship1').append('<option value="' + value + '">' + value + '</option>');
            });
            $('#table_ship1').prop('disabled', false).selectpicker('refresh');
            $('#field_ship1').empty().append('<option value="">请先选择数据表</option>').prop('disabled', true).selectpicker('refresh');
        });

        $('#table_ship1').change(function() {
            var selecteddb_ship1 = $('#db_ship1').val();
            var selectedtable_ship1 = $(this).val();
            var field_ship1 = [];
            $.each(dbJson, function(key, value) {
                if (value.db === selecteddb_ship1 && value.table === selectedtable_ship1) {
                    field_ship1.push(value.field);
                }
            });
            $('#field_ship1').empty().append('<option value="">请选择字段</option>');
            $.each(field_ship1, function(key, value) {
                $('#field_ship1').append('<option value="' + value + '">' + value + '</option>');
            });
            $('#field_ship1').prop('disabled', false).selectpicker('refresh');
        });

        $('#field_ship1').change(function() {
            var selecteddb_ship1 = $('#db_ship1').val();
            var selectedtable_ship1 = $('#table_ship1').val();
            var selectedfield_ship1 = $(this).val();
            $.each(dbJson, function(key, value) {
                if (value.db === selecteddb_ship1 && value.table === selectedtable_ship1 && value.field === selectedfield_ship1) {
                    $('#hiddenId_ship1').val(value.id);
                }
            });
        });

        ///////////////////////////////////////////////////********************************************//////////////
        $('#db_ship2').change(function() {
            var selecteddb_ship1 = $(this).val();
            var table_ship2 = [];
            $.each(dbJson, function(key, value) {
                if (value.db === selecteddb_ship1) {
                    if ($.inArray(value.table, table_ship2) === -1) {
                        table_ship2.push(value.table);
                    }
                }
            });
            $('#table_ship2').empty().append('<option value="">请选择数据表</option>');
            $.each(table_ship2, function(key, value) {
                $('#table_ship2').append('<option value="' + value + '">' + value + '</option>');
            });
            $('#table_ship2').prop('disabled', false).selectpicker('refresh');
            $('#field_ship2').empty().append('<option value="">请先选择数据表</option>').prop('disabled', true).selectpicker('refresh');
        });

        $('#table_ship2').change(function() {
            var selecteddb_ship1 = $('#db_ship2').val();
            var selectedtable_ship1 = $(this).val();
            var field_ship2 = [];
            $.each(dbJson, function(key, value) {
                if (value.db === selecteddb_ship1 && value.table === selectedtable_ship1) {
                    field_ship2.push(value.field);
                }
            });
            $('#field_ship2').empty().append('<option value="">请选择字段</option>');
            $.each(field_ship2, function(key, value) {
                $('#field_ship2').append('<option value="' + value + '">' + value + '</option>');
            });
            $('#field_ship2').prop('disabled', false).selectpicker('refresh');
        });

        $('#field_ship2').change(function() {
            var selecteddb_ship2 = $('#db_ship2').val();
            var selectedtable_ship2 = $('#table_ship2').val();
            var selectedfield_ship2 = $(this).val();
            $.each(dbJson, function(key, value) {
                if (value.db === selecteddb_ship2 && value.table === selectedtable_ship2 && value.field === selectedfield_ship2) {
                    $('#hiddenId_ship2').val(value.id);
                }
            });
        });


    });
    function query() {
        var id = $('#hiddenId').val();
        if (id === '') {
            alert('请先选择数据库、数据表和字段！');
            return;
        }
        $.ajax({
            type : "get",
            async:true,
            cache : false,
            url: serverURL+'getRelationshipByFieldId?id='+id,
            success: function(data) {
                var html = '';
                $("#result").empty();
                $("#result_table").show();
                queryResult = data;
                populateTable(data);
               /* $.each(data, function(key, value) {

                    html += '<div>id: ' + value.id + ', db: ' + value.db + ', table: ' + value.table + ', field: ' + value.field + ', note: ' + value.note + '</div><br>';
                });
                $('#result').html(html);*/

            },
            error: function(xhr, status, error) {
                alert('查询失败：' + error);
            }
        });
    }

    function reset() {
        $('#db').val('').prop('disabled', false).selectpicker('refresh');
        $('#table').empty().append('<option value="">请先选择数据库</option>').prop('disabled', true).selectpicker('refresh');
        $('#field').empty().append('<option value="">请先选择数据表</option>').prop('disabled', true).selectpicker('refresh');
        $('#hiddenId').val('');
        $("#result").empty();
        $("#result_table").hide();
        //$('#result').bootstrapTable('destroy');
    }

    $('#field').on('shown.bs.select', function() {
        $('#field').selectpicker('deselectAll');
        $('#field').data('selectpicker').$searchbox.focus();
        $('#field').data('selectpicker').$searchbox.on('input', function() {
            var input = $(this).val().toLowerCase();
            $('#field option').each(function() {
                if ($(this).text().toLowerCase().indexOf(input) === -1) {
                    $(this).hide();
                } else {
                    $(this).show();
                }
            });
            $('#field').data('selectpicker').$menuInner.scrollTop(0);
        });
    });

    $('#field').on('hidden.bs.select', function() {
        $('#field option').each(function() {
            $(this).show();
        });
    });
    // Function to generate HTML for each row in the table
    function generateTableRow(data) {
        return "<tr>" +
            "<td>" + data.id + "</td>" +
            "<td>" + data.db + "</td>" +
            "<td>" + data.table + "</td>" +
            "<td>" + data.field + "</td>" +
            "<td>" + data.note + "</td>" +
            "<td><button class='btn btn-danger' onclick='deleteRow(" + data.id + ")'>删除</button></td>" +
            "</tr>";
    }

    // Function to populate the table with data from the JSON array
    function populateTable(data) {
        // Clear the table body
        $("#result").empty();

        // Loop through the data array and append each row to the table
        for (var i = 0; i < data.length; i++) {
            $("#result").append(generateTableRow(data[i]));
        }
    }

    // Function to delete a row from the table based on the ID
    function deleteRow(id) {
        console.log(id);
        if (confirm("Are you sure you want to delete this row?")) {
            // Send a POST request to the server with the ID as a parameter
            $.post(serverURL+'deleteRelationShipByFieldId', { id: id })
                .done(function(data) {
                    query();
                })
                .fail(function(error) {
                    // If the request failed, show an error message
                    alert("Failed to delete row. Error message: " + error.message);
                });
        }
    }



    function creatShip() {
        var shipId1 = $('#hiddenId_ship1').val();
        var shipId2 = $('#hiddenId_ship2').val();
        if (shipId1 === '' || shipId2 === '') {
            alert('请先选择数据库、数据表和字段！');
            return;
        }
        $.ajax({
            type : "POST",
            async:true,
            cache : false,
            url: serverURL+'addRelationShipByFieldId',
            data:{"field1":shipId1,"field2":shipId2},
            success: function(data) {
                if (data == 0 ){
                    // 创建提示框元素
                    var alert = $('<div>').addClass('alert alert-warning').text('关联规则已存在');
                    // 将提示框添加到页面中
                    $('body').append(alert);
                    // 2秒后移除提示框
                    setTimeout(function(){
                        alert.remove();
                    }, 2000);
                }
                if (data == 1){
                    // 创建提示框元素
                    var alert = $('<div>').addClass('alert alert-success').text('添加成功');
                    // 将提示框添加到页面中
                    $('body').append(alert);
                    // 2秒后移除提示框
                    setTimeout(function(){
                        alert.remove();
                    }, 2000);
                }
            },
            error: function(xhr, status, error) {
                alert('查询失败：' + error);
            }
        });
    }



    /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    $('#db_ship_tree1').change(function() {
        var selecteddb_ship1 = $(this).val();
        var table_ship_tree1 = [];
        $.each(dbJson, function(key, value) {
            if (value.db === selecteddb_ship1) {
                if ($.inArray(value.table, table_ship_tree1) === -1) {
                    table_ship_tree1.push(value.table);
                }
            }
        });
        $('#table_ship_tree1').empty().append('<option value="">请选择数据表</option>');
        $.each(table_ship_tree1, function(key, value) {
            $('#table_ship_tree1').append('<option value="' + value + '">' + value + '</option>');
        });
        $('#table_ship_tree1').prop('disabled', false).selectpicker('refresh');
        $('#field_ship_tree1').empty().append('<option value="">请先选择数据表</option>').prop('disabled', true).selectpicker('refresh');
    });

    $('#table_ship_tree1').change(function() {
        var selecteddb_ship1 = $('#db_ship_tree1').val();
        var selectedtable_ship1 = $(this).val();
        var field_ship_tree1 = [];
        $.each(dbJson, function(key, value) {
            if (value.db === selecteddb_ship1 && value.table === selectedtable_ship1) {
                field_ship_tree1.push(value.field);
            }
        });
        $('#field_ship_tree1').empty().append('<option value="">请选择字段</option>');
        $.each(field_ship_tree1, function(key, value) {
            $('#field_ship_tree1').append('<option value="' + value + '">' + value + '</option>');
        });
        $('#field_ship_tree1').prop('disabled', false).selectpicker('refresh');
    });

    $('#field_ship_tree1').change(function() {
        var selecteddb_ship1 = $('#db_ship_tree1').val();
        var selectedtable_ship1 = $('#table_ship_tree1').val();
        var selectedfield_ship1 = $(this).val();
        $.each(dbJson, function(key, value) {
            if (value.db === selecteddb_ship1 && value.table === selectedtable_ship1 && value.field === selectedfield_ship1) {
                $('#hiddenId_ship1').val(value.id);
            }
        });
    });

    ///////////////////////////////////////////////////********************************************//////////////
    $('#db_ship_tree2').change(function() {
        var selecteddb_ship1 = $(this).val();
        var table_ship_tree2 = [];
        $.each(dbJson, function(key, value) {
            if (value.db === selecteddb_ship1) {
                if ($.inArray(value.table, table_ship_tree2) === -1) {
                    table_ship_tree2.push(value.table);
                }
            }
        });
        $('#table_ship_tree2').empty().append('<option value="">请选择数据表</option>');
        $.each(table_ship_tree2, function(key, value) {
            $('#table_ship_tree2').append('<option value="' + value + '">' + value + '</option>');
        });
        $('#table_ship_tree2').prop('disabled', false).selectpicker('refresh');
        $('#field_ship_tree2').empty().append('<option value="">请先选择数据表</option>').prop('disabled', true).selectpicker('refresh');
    });

    $('#table_ship_tree2').change(function() {
        var selecteddb_ship1 = $('#db_ship_tree2').val();
        var selectedtable_ship1 = $(this).val();
        var field_ship_tree2 = [];
        $.each(dbJson, function(key, value) {
            if (value.db === selecteddb_ship1 && value.table === selectedtable_ship1) {
                field_ship_tree2.push(value.field);
            }
        });
        $('#field_ship_tree2').empty().append('<option value="">请选择字段</option>');
        $.each(field_ship_tree2, function(key, value) {
            $('#field_ship_tree2').append('<option value="' + value + '">' + value + '</option>');
        });
        $('#field_ship_tree2').prop('disabled', false).selectpicker('refresh');
    });

    $('#field_ship_tree2').change(function() {
        var selecteddb_ship2 = $('#db_ship_tree2').val();
        var selectedtable_ship2 = $('#table_ship_tree2').val();
        var selectedfield_ship2 = $(this).val();
        $.each(dbJson, function(key, value) {
            if (value.db === selecteddb_ship2 && value.table === selectedtable_ship2 && value.field === selectedfield_ship2) {
                $('#hiddenId_ship2').val(value.id);
            }
        });
    });


    function searchTree() {
        var selecteddb_ship1 = $('#db_ship_tree1').val();
        var selectedtable_ship1 = $('#table_ship_tree1').val();
        var selectedfield_ship1 = $('#field_ship_tree1').val();
        var selecteddb_ship2 = $('#db_ship_tree2').val();
        var selectedtable_ship2 = $('#table_ship_tree2').val();
        var selectedfield_ship2 = $('#field_ship_tree2').val();
        var id = $('#hiddenId').val();
        if (selecteddb_ship1 == ''|| selectedtable_ship1 == '' ||selecteddb_ship2 == ''|| selectedtable_ship2 == '') {
            alert('请先选择数据库和表必选！！！！！');
            return;
        }
        data =  {
            "db1": selecteddb_ship1,
            "table1": selectedtable_ship1,
            "field1": selectedfield_ship1,
            "db2": selecteddb_ship2,
            "table2": selectedtable_ship2,
            "field2": selectedfield_ship2
        };
        $.ajax({
            type : "post",
            async:true,
            cache : false,
            data  : JSON.stringify(data) ,
            url: serverURL+'findRelationship',
            dataType: "json",
            contentType: 'application/json',
            success: function(data) {
                console.log(data);
               creatTree(data);
            },
            error: function(xhr, status, error) {
                alert('查询失败：' + error);
            }
        });
    }
    function creatTree(data) {
        $("#outputTree").empty();
        $.each(data, function(index, data) {
            $('#outputTree').append('<li style="margin-top: 8px">' + data + '</li>');
        });

    }



</script>

</body>
</html>

